using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Entity;
using APP.QUANLY.Entity.ConditionForm;

namespace APP.QUANLY.DAL
{
    /// <summary>
    /// Summary description for KHACHHANGTHEODOIDOANHTHU.
     /// </summary>
	public class KHACHHANGTHEODOIDOANHTHUDao
    {
	    private string _SQLSelectByKey;
	    private string _SQLUpdate;
	    private string _SQLInsert;
	    private string _SQLDelete;
	    private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion
        
        #region Constructor

        public KHACHHANGTHEODOIDOANHTHUDao()
        {
            _dbConnection = null;
		    InitSqlQuery();
        }

        public KHACHHANGTHEODOIDOANHTHUDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
       {
            string query;

            query = @"SELECT MAKHACHHANG, NAM, THANG1, THANG2, THANG3, THANG4, THANG5, THANG6, THANG7, THANG8, THANG9, THANG10, THANG11, THANG12, THEODOI, NGUOICAPNHAT FROM KHACHHANGTHEODOIDOANHTHU WHERE (MAKHACHHANG = @MAKHACHHANG AND 
NAM = @NAM)";
	        _SQLSelectByKey = query;

            query = @"UPDATE KHACHHANGTHEODOIDOANHTHU SET THANG1 = @THANG1, THANG2 = @THANG2, THANG3 = @THANG3, THANG4 = @THANG4, THANG5 = @THANG5, THANG6 = @THANG6, THANG7 = @THANG7, THANG8 = @THANG8, THANG9 = @THANG9, THANG10 = @THANG10, THANG11 = @THANG11, THANG12 = @THANG12, THEODOI = @THEODOI, NGUOICAPNHAT = @NGUOICAPNHAT WHERE (MAKHACHHANG = @MAKHACHHANG AND 
NAM = @NAM)";
	        _SQLUpdate = query;

            query = @"INSERT INTO KHACHHANGTHEODOIDOANHTHU VALUES (@MAKHACHHANG, @NAM, @THANG1, @THANG2, @THANG3, @THANG4, @THANG5, @THANG6, @THANG7, @THANG8, @THANG9, @THANG10, @THANG11, @THANG12, @THEODOI, @NGUOICAPNHAT)";
	        _SQLInsert = query;

            query = @"DELETE FROM KHACHHANGTHEODOIDOANHTHU WHERE (MAKHACHHANG = @MAKHACHHANG AND 
NAM = @NAM)";
	        _SQLDelete = query;

            query = @"SELECT MAKHACHHANG, NAM, THANG1, THANG2, THANG3, THANG4, THANG5, THANG6, THANG7, THANG8, THANG9, THANG10, THANG11, THANG12, THEODOI, NGUOICAPNHAT FROM KHACHHANGTHEODOIDOANHTHU";
	        _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

				if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(KHACHHANGTHEODOIDOANHTHU obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                         DalTools.addDbDataParameter(command, "MAKHACHHANG", obj.MAKHACHHANG, DbType.Guid);
         DalTools.addDbDataParameter(command, "NAM", obj.NAM, DbType.Int32);
                         DalTools.addDbDataParameter(command, "THANG1", obj.THANG1, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG2", obj.THANG2, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG3", obj.THANG3, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG4", obj.THANG4, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG5", obj.THANG5, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG6", obj.THANG6, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG7", obj.THANG7, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG8", obj.THANG8, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG9", obj.THANG9, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG10", obj.THANG10, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG11", obj.THANG11, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANG12", obj.THANG12, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THEODOI", obj.THEODOI, DbType.Boolean);
         DalTools.addDbDataParameter(command, "NGUOICAPNHAT", obj.NGUOICAPNHAT, DbType.String);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(KHACHHANGTHEODOIDOANHTHU obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                         DalTools.addDbDataParameter(command, "MAKHACHHANG", obj.MAKHACHHANG, DbType.Guid);
         DalTools.addDbDataParameter(command, "NAM", obj.NAM, DbType.Int32);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(KHACHHANGTHEODOIDOANHTHU obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(KHACHHANGTHEODOIDOANHTHU obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties
     
        private KHACHHANGTHEODOIDOANHTHUList SelectListByCommand(IDbCommand command)
        {
            KHACHHANGTHEODOIDOANHTHUList list = new KHACHHANGTHEODOIDOANHTHUList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				KHACHHANGTHEODOIDOANHTHU obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new KHACHHANGTHEODOIDOANHTHU();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
		
        private KHACHHANGTHEODOIDOANHTHUList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            KHACHHANGTHEODOIDOANHTHUList list = new KHACHHANGTHEODOIDOANHTHUList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				KHACHHANGTHEODOIDOANHTHU obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
				command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new KHACHHANGTHEODOIDOANHTHU();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
	
        private void ReadProperties(KHACHHANGTHEODOIDOANHTHU obj, IDataReader reader)
        {
            try
            {
                                if(!(reader["MAKHACHHANG"] is DBNull))
		{
			obj.MAKHACHHANG = (Guid)reader["MAKHACHHANG"];
		}
                if(!(reader["NAM"] is DBNull))
		{
			obj.NAM = (Int32)reader["NAM"];
		}
                if(!(reader["THANG1"] is DBNull))
		{
			obj.THANG1 = (decimal)reader["THANG1"];
		}
                if(!(reader["THANG2"] is DBNull))
		{
			obj.THANG2 = (decimal)reader["THANG2"];
		}
                if(!(reader["THANG3"] is DBNull))
		{
			obj.THANG3 = (decimal)reader["THANG3"];
		}
                if(!(reader["THANG4"] is DBNull))
		{
			obj.THANG4 = (decimal)reader["THANG4"];
		}
                if(!(reader["THANG5"] is DBNull))
		{
			obj.THANG5 = (decimal)reader["THANG5"];
		}
                if(!(reader["THANG6"] is DBNull))
		{
			obj.THANG6 = (decimal)reader["THANG6"];
		}
                if(!(reader["THANG7"] is DBNull))
		{
			obj.THANG7 = (decimal)reader["THANG7"];
		}
                if(!(reader["THANG8"] is DBNull))
		{
			obj.THANG8 = (decimal)reader["THANG8"];
		}
                if(!(reader["THANG9"] is DBNull))
		{
			obj.THANG9 = (decimal)reader["THANG9"];
		}
                if(!(reader["THANG10"] is DBNull))
		{
			obj.THANG10 = (decimal)reader["THANG10"];
		}
                if(!(reader["THANG11"] is DBNull))
		{
			obj.THANG11 = (decimal)reader["THANG11"];
		}
                if(!(reader["THANG12"] is DBNull))
		{
			obj.THANG12 = (decimal)reader["THANG12"];
		}
                if(!(reader["THEODOI"] is DBNull))
		{
			obj.THEODOI = (bool)reader["THEODOI"];
		}
                if(!(reader["NGUOICAPNHAT"] is DBNull))
		{
			obj.NGUOICAPNHAT = (string)reader["NGUOICAPNHAT"];
		}
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion
      
        public KHACHHANGTHEODOIDOANHTHU SelectByKey(Guid MAKHACHHANG, Int32 NAM)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "MAKHACHHANG", MAKHACHHANG, DbType.Guid);
         DalTools.addDbDataParameter(command, "NAM", NAM, DbType.Int32);
      	    KHACHHANGTHEODOIDOANHTHUList list =  SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public KHACHHANGTHEODOIDOANHTHU SelectByKey(Guid MAKHACHHANG, Int32 NAM, IDbTransaction trans)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "MAKHACHHANG", MAKHACHHANG, DbType.Guid);
         DalTools.addDbDataParameter(command, "NAM", NAM, DbType.Int32);
      	    KHACHHANGTHEODOIDOANHTHUList list =  SelectListByCommand(command, trans);
			
            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }
		
        public KHACHHANGTHEODOIDOANHTHUList SelectAll()
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectAll;
      	    KHACHHANGTHEODOIDOANHTHUList list =  SelectListByCommand(command);
	        return list;
        }

         public KHACHHANGTHEODOIDOANHTHUList SelectByCommand(string Command)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = Command;
      	    KHACHHANGTHEODOIDOANHTHUList list =  SelectListByCommand(command);
	        return list;
        }

        public KHACHHANGTHEODOIDOANHTHUList SelectByCondition(KHACHHANGTHEODOIDOANHTHUConditionForm condt)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM KHACHHANGTHEODOIDOANHTHU " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            KHACHHANGTHEODOIDOANHTHUList list = SelectListByCommand(command);
            return list;
        }

        private void DeleteByCondition(KHACHHANGTHEODOIDOANHTHUConditionForm condt, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM KHACHHANGTHEODOIDOANHTHU " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(KHACHHANGTHEODOIDOANHTHUConditionForm obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            	if (obj.IsSetMAKHACHHANG)
	{
    	    s += " AND MAKHACHHANG = @MAKHACHHANG ";
	}
	if (obj.IsSetNAM)
	{
    	    s += " AND NAM = @NAM ";
	}
	if (obj.IsSetTHANG1)
	{
    	    s += " AND THANG1 = @THANG1 ";
	}
	if (obj.IsSetTHANG2)
	{
    	    s += " AND THANG2 = @THANG2 ";
	}
	if (obj.IsSetTHANG3)
	{
    	    s += " AND THANG3 = @THANG3 ";
	}
	if (obj.IsSetTHANG4)
	{
    	    s += " AND THANG4 = @THANG4 ";
	}
	if (obj.IsSetTHANG5)
	{
    	    s += " AND THANG5 = @THANG5 ";
	}
	if (obj.IsSetTHANG6)
	{
    	    s += " AND THANG6 = @THANG6 ";
	}
	if (obj.IsSetTHANG7)
	{
    	    s += " AND THANG7 = @THANG7 ";
	}
	if (obj.IsSetTHANG8)
	{
    	    s += " AND THANG8 = @THANG8 ";
	}
	if (obj.IsSetTHANG9)
	{
    	    s += " AND THANG9 = @THANG9 ";
	}
	if (obj.IsSetTHANG10)
	{
    	    s += " AND THANG10 = @THANG10 ";
	}
	if (obj.IsSetTHANG11)
	{
    	    s += " AND THANG11 = @THANG11 ";
	}
	if (obj.IsSetTHANG12)
	{
    	    s += " AND THANG12 = @THANG12 ";
	}
	if (obj.IsSetTHEODOI)
	{
    	    s += " AND THEODOI = @THEODOI ";
	}
	if (obj.IsSetNGUOICAPNHAT)
	{
    	    s += " AND NGUOICAPNHAT = @NGUOICAPNHAT ";
	}
            if (s.Length > 0)
            {
                s =" WHERE "+s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, KHACHHANGTHEODOIDOANHTHUConditionForm obj)
        {
	    if (obj == null) { return;}
            	            if (obj.IsSetMAKHACHHANG)
            {
            	DalTools.addDbDataParameter(command, "MAKHACHHANG", obj.MAKHACHHANG, DbType.Guid); 
            }
            if (obj.IsSetNAM)
            {
            	DalTools.addDbDataParameter(command, "NAM", obj.NAM, DbType.Int32); 
            }
            if (obj.IsSetTHANG1)
            {
            	DalTools.addDbDataParameter(command, "THANG1", obj.THANG1, DbType.Decimal); 
            }
            if (obj.IsSetTHANG2)
            {
            	DalTools.addDbDataParameter(command, "THANG2", obj.THANG2, DbType.Decimal); 
            }
            if (obj.IsSetTHANG3)
            {
            	DalTools.addDbDataParameter(command, "THANG3", obj.THANG3, DbType.Decimal); 
            }
            if (obj.IsSetTHANG4)
            {
            	DalTools.addDbDataParameter(command, "THANG4", obj.THANG4, DbType.Decimal); 
            }
            if (obj.IsSetTHANG5)
            {
            	DalTools.addDbDataParameter(command, "THANG5", obj.THANG5, DbType.Decimal); 
            }
            if (obj.IsSetTHANG6)
            {
            	DalTools.addDbDataParameter(command, "THANG6", obj.THANG6, DbType.Decimal); 
            }
            if (obj.IsSetTHANG7)
            {
            	DalTools.addDbDataParameter(command, "THANG7", obj.THANG7, DbType.Decimal); 
            }
            if (obj.IsSetTHANG8)
            {
            	DalTools.addDbDataParameter(command, "THANG8", obj.THANG8, DbType.Decimal); 
            }
            if (obj.IsSetTHANG9)
            {
            	DalTools.addDbDataParameter(command, "THANG9", obj.THANG9, DbType.Decimal); 
            }
            if (obj.IsSetTHANG10)
            {
            	DalTools.addDbDataParameter(command, "THANG10", obj.THANG10, DbType.Decimal); 
            }
            if (obj.IsSetTHANG11)
            {
            	DalTools.addDbDataParameter(command, "THANG11", obj.THANG11, DbType.Decimal); 
            }
            if (obj.IsSetTHANG12)
            {
            	DalTools.addDbDataParameter(command, "THANG12", obj.THANG12, DbType.Decimal); 
            }
            if (obj.IsSetTHEODOI)
            {
            	DalTools.addDbDataParameter(command, "THEODOI", obj.THEODOI, DbType.Boolean); 
            }
            if (obj.IsSetNGUOICAPNHAT)
            {
            	DalTools.addDbDataParameter(command, "NGUOICAPNHAT", obj.NGUOICAPNHAT, DbType.String); 
            }
        }
        #endregion
    }
}